﻿// ====================================================================================================================
// Project: Camstar InSiteExtractor
// Source File: InSiteExtractor.cs
// Author: Roop Sin LE
// Created(MM/DD/YYYY HH:MM): 08/13/2010 9:14:21 PM
// Copyright: Camstar Inc.
// ====================================================================================================================

using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.IO;
using Camstar.Utils.csv;
using InSiteExtractor.Data;
using log4net;
using InSiteExtractor.Definition;

namespace InSiteExtractor
{
    /// <summary>
    /// Data Extractor Manager to hold all objects in the program
    /// </summary>
    internal class InSiteDataExtractorManager
    {
        // Define a static logger variable so that it references the
        private static readonly ILog Log = LogManager.GetLogger(typeof(InSiteDataExtractorManager));
        private static InSiteDataExtractorManager _instance;
        private StatusMonitor _statusMonitor;


        #region Properties
        public StatusMonitor Monitor
        {
            set { _statusMonitor = value; }
            get { return _statusMonitor; }
        }
        #endregion

        protected InSiteDataExtractorManager()
        {
            // Configuration Manager
            _statusMonitor = new StatusMonitor();
        }

        /// <summary>
        /// Get Singleton Manager to process 
        /// </summary>
        /// <returns></returns>
        public static InSiteDataExtractorManager Instance()
        {
            return _instance ?? (_instance = new InSiteDataExtractorManager());
        }

        /// <summary>
        /// Initiliaze the class
        /// </summary>
        public void Initialize()
        {
        }

        /// <summary>
        /// Extract data from list of SQL script
        /// </summary>
        /// <param name="files">Indicate the list of excel for extracting</param>
        /// <param name="excelFolder">Indicate the folder consists of SEMI excel template</param>
        /// <param name="csvFilters">Indicate csv product file for filtering</param>
        public void ExtractData(FileInfo[] files, string excelFolder, Dictionary<string, string> csvFilters)
        {
            Dictionary<string, string> filters = new Dictionary<string, string>();

            foreach (KeyValuePair<string, string> item in csvFilters)
            {
                string filterStr = "";
                // Processing to retrive product selected.
                //
                // Create a CSV reader
                // Using a CSV reader for the file MyFile.csv...
                using (var reader = new CsvReader(item.Value))
                {

                    // Using a data table read by the reader...
                    using (DataTable table = reader.ReadTable(false))
                    {
                        // Loop through all of the rows...
                        foreach (DataRow row in table.Rows)
                        {
                            // This is a selected product for extracting
                            if (row[0] != null && row[0].ToString() != "")
                            {
                                filterStr += string.Format("'{0}',", row[0]);
                            }
                        } // Loop through all of the rows...
                    } // Using a data table read by the reader...

                    if (filterStr != "")
                    {
                        // Make up the string with the format ('...', '...')
                        filterStr = string.Format(" IN ({0}) ", filterStr.Substring(0, filterStr.Length - 1));
                    }
                    else
                    {
                        filterStr = " like '%' ";
                    }

                    Log.Debug(filterStr);
                }

                filters[item.Key] = filterStr;
            }

            _statusMonitor.WorkNumber = files.Length;
            foreach (FileInfo f in files)
            {
                try
                {
                    string[] group = f.Name.Split('.');
                    if (group.Length <= 2)
                    {
                        Log.ErrorFormat(
                            "SqlScript file name [{0}] is not following the naming convension. It must be named as <Excel Loader File>.<Sheet Name>.xsl.",
                            f.Name);
                        continue;
                    }
                    string sheetName = group[group.Length - 2];
                    string excelFullName =
                        new FileInfo(Path.Combine(excelFolder,
                                                  f.Name.Replace(string.Format(".{0}.sql", sheetName), ".xls")))
                            .FullName;

                    //Inserted by wesley for checking xlsm file (used for Workflow with huge records exceed 65535)- 2011-9-22
                    if (!File.Exists(excelFullName))
                    {
                        excelFullName = new FileInfo(Path.Combine(excelFolder, f.Name.Replace(string.Format(".{0}.sql", sheetName), ".xlsm"))).FullName;
                    }

                    //
                    Log.InfoFormat("Processing the script <{0}> and update the excel file <{1}> on <{2}>",
                                   f.Name, excelFullName, sheetName);
                    ExtractData(f.FullName, excelFullName, sheetName, filters);
                }
                catch (Exception ex)
                {
                    Log.Error("Error occured during processing. Please check the error : " + ex.Message);
                }
            }
        }

        /// <summary>
        /// This method is used to extract data from input SQL and update the data into SEMI excel loader
        /// </summary>
        /// <param name="scriptFile">Indicate the sql scrip to extract</param>
        /// <param name="excelFile">Indicate SEMI excel loader to be updated</param>
        /// <param name="sheetName">Indicate which sheet to be updated</param>
        /// <param name="filters">Indicate set of pair (key, value) for replace in SQL Statment</param>
        /// <returns></returns>
        private int ExtractData(string scriptFile, string excelFile, string sheetName, Dictionary<string, string> filters)
        {
            int retCode = 0;

            _statusMonitor.FieldProcessing = new FileInfo(scriptFile).Name;

            // Excel Writer
            var excelSheetUpdator = new ExcelWorkbookUpdator(excelFile);
            excelSheetUpdator.Open();
            excelSheetUpdator.SetSheet(sheetName);
            excelSheetUpdator.Initialize();

            var streamReader = new StreamReader(scriptFile);
            string script = streamReader.ReadToEnd();
            streamReader.Close();
            Log.DebugFormat("Script: {0}", script);

            foreach (KeyValuePair<string, string> item in filters)
            {
                script = script.Replace(string.Format("${0}$", item.Key), item.Value);
            }
            Log.Debug(script);

            int rowsUpdated = SqlDataExtractor.ExecuteReader(script, excelSheetUpdator);

            excelSheetUpdator.Close(true);
            _statusMonitor.UpdatedItemNumber = rowsUpdated;
            _statusMonitor.WorkCompleted = _statusMonitor.WorkCompleted + 1;

            return retCode;
        }

        /// <summary>
        /// This method is used to generate SQL Template consits of column name correspond to SEMI excel loaders
        /// </summary>
        /// <param name="excelFolder">Indicate the excel folder</param>
        /// <param name="selectedSheetNames">Indicate selected sheet names to process</param>
        /// <param name="sqlFolder">Indicate the SQL Folder to store out</param>
        /// <returns>Return value. 0 for successful, otherwise for failed.</returns>
        public int GenerateSqlScript(string excelFolder, string selectedSheetNames, string sqlFolder)
        {
            int retCode = 0;

            // There are two cases:
            //   01. Input as folder
            //   02. Input as selected files 

            // Extract searchCriteria
            string excelDir = Path.GetDirectoryName(excelFolder);
            if (excelDir == "") // Not root ==> it is as a folder
            {
                excelDir = Path.GetFullPath(excelFolder);
            }
            var dir = new DirectoryInfo(excelDir);
            string searchCriteria = Path.GetFileName(excelFolder);
            if (searchCriteria == excelFolder)
                searchCriteria = "*.*";

            _statusMonitor.WorkNumber = dir.GetFiles(searchCriteria).Length;
            foreach (FileInfo f in dir.GetFiles(searchCriteria))
            {
                if (ConfigurationManager.AppSettings[ExtractorConst.IgnoredWorkbooks] != null)
                {
                    var ignoreList = new List<String>(ConfigurationManager.AppSettings[ExtractorConst.IgnoredWorkbooks].Split(','));
                    if (ignoreList.Contains(f.Name))
                    {
                        _statusMonitor.FieldProcessing = string.Format("{0} was ignore.", f.Name);
                        _statusMonitor.WorkCompleted = _statusMonitor.WorkCompleted + 1;
                        continue;
                    }
                }

                Log.InfoFormat("{0} - Processing ...........", f.Name);
                _statusMonitor.FieldProcessing = f.Name;
                var workBook = new ExcelWorkbookUpdator(f.FullName);
                workBook.Initialize();
                workBook.Open();

                // Processing each sheet name
                foreach (string sheetName in workBook.GetSheetNames())
                {
                    try
                    {
                        if (ConfigurationManager.AppSettings[ExtractorConst.IgnoredSheets] != null)
                        {
                            var ignoreList = new List<String>(ConfigurationManager.AppSettings[ExtractorConst.IgnoredSheets].Split(','));
                            if (ignoreList.Contains(sheetName))
                            {
                                continue;
                            }
                        }

                        if (selectedSheetNames != null && selectedSheetNames.Split(',').Length > 0)
                        {
                            if (!selectedSheetNames.Contains(sheetName))
                            {
                                continue;
                            }
                        }

                        Log.InfoFormat("{0} [Sheet = {1}] - Processing ...........", f.Name, sheetName);
                        _statusMonitor.FieldProcessing = string.Format("{0} [Sheet = {1}]", f.Name, sheetName);
                        workBook.SetSheet(sheetName);
                        string scriptName = string.Format(@"{0}\{1}.{2}.sql", sqlFolder, f.Name.Replace(".xls", ""),
                                                          sheetName);
                        using (TextWriter file = new StreamWriter(scriptName))
                        {
                            file.WriteLine("{0}", workBook.GenerateSqlTemplate());
                        }

                        Log.DebugFormat("{0} [Sheet = {1}] - Generate [{2}] with context as [{3}]", f.Name, sheetName,
                                        scriptName, workBook.GenerateSqlTemplate());
                    }
                    catch (Exception ex)
                    {
                        Log.Error(ex);
                    }
                }

                // Close the workbook and free memory
                workBook.Close(false);
                _statusMonitor.WorkCompleted = _statusMonitor.WorkCompleted + 1;
            }

            return retCode;
        }


        /// <summary>
        /// This method is used to generate SQL Template consits of column name correspond to SEMI excel loaders
        /// </summary>
        /// <param name="sqlFile">Indicate the SQL file to process</param>
        /// /// <param name="textFile">Indicate the file to write</param>
        /// <returns>Return value. 0 for successful, otherwise for failed.</returns>
        public int Write2Text(string sqlFile, string textFile)
        {
            int retCode = 0;
            Log.InfoFormat("Processing SQL Script [{0}] and write into text file [{1}]", sqlFile, textFile);
            try
            {
                var streamReader = new StreamReader(sqlFile);
                string script = streamReader.ReadToEnd();
                streamReader.Close();

                
                using (TextWriter file = new StreamWriter(textFile))
                {
                    CsvWriter writer = new CsvWriter(file);
                    DataTable table = SqlDataExtractor.ExecuteReader(script);
                    writer.WriteTable(table, true);
                }
            }
            catch (Exception ex)
            {
                Log.Error(ex);
            }

            return retCode;
        }
    }
}